package com.frogs.ucenter.export;

import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

import javax.annotation.Resource;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.jsoup.Connection.Method;
import org.jsoup.Jsoup;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Component;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.frogs.framework.util.StringUtil;
import com.frogs.framework.util.date.DateUtil;
import com.frogs.system.util.ApiUrlParamEnum;
import com.frogs.system.util.GoodsEvaluateEnum;
import com.frogs.system.util.UserAccountInfoStatusEnum;
import com.frogs.system.util.UserTypeEnum;
import com.frogs.system.web.api.response.Response.ApiResultEnum;
import com.frogs.system.web.api.response.Response.ResultParamEnum;
import com.frogs.ucenter.dao.GoodsEvaluateDao;
import com.frogs.ucenter.dao.UserAccountInfoDao;
import com.frogs.ucenter.dao.UserBaseInfoDao;
import com.frogs.ucenter.dao.UserLevelInfoDao;
import com.frogs.ucenter.entity.GoodsEvaluate;
import com.frogs.ucenter.entity.UserAccountInfo;
import com.frogs.ucenter.entity.UserBaseInfo;
import com.frogs.ucenter.entity.UserLevelInfo;

/**
 * 导入评价数据
 *
 * @author lijian
 * @version 2014年8月12日 - 下午3:44:08
 */
@Component("importGoodsEvaluateData")
public class ImportGoodsEvaluateData {

	private static Logger log = LoggerFactory.getLogger(ImportGoodsEvaluateData.class);
	
	private List<Integer> goodsIdList = new ArrayList<Integer>();
	
	@Resource
    private JdbcTemplate jdbcTemplate;
	
	@Resource
	private GoodsEvaluateDao goodsEvaluateDao;
	@Resource
	private UserBaseInfoDao userBaseInfoDao;
	@Resource
	private UserLevelInfoDao userLevelInfoDao;
	@Resource
	private UserAccountInfoDao userAccountInfoDao;
	@Resource
	private CleanEvaluateCache cleanEvaluateCache;
	
	@Async
    public void importIn(String path, String startDate, String endDate){
    	File importFile = new File(path);
    	log.info("func[importIn] importFile: " + importFile);
  		EvaluateObj evaluateObj = null ; 
  		int row = 1;
  		try{
            Workbook wb = WorkbookFactory.create(importFile);
            Sheet sheet = wb.getSheetAt(0);
            int endRow = sheet.getLastRowNum();
            for(; row<=endRow; row++){
            	Row row2 = sheet.getRow(row);
            	if (row2.getCell(1) == null) break;
            	evaluateObj = importRead(sheet,row);
            	handleEvaluateObj(evaluateObj);
            	insertGoodsEvaluate(evaluateObj, startDate, endDate);
            }
        }catch (Exception e){
            log.error("evaluateObj: " + evaluateObj + ", row: " + row, e);
        }
  		log.info("func[importIn] goodsIdList[" + goodsIdList + "]");
        if (goodsIdList != null && goodsIdList.size() > 0) {
        	cleanEvaluateCache.cleanEvaluateCache(null, goodsIdList);
        }
    }
	
	public Map<String, Object> verifyForm(String path) {
		Map<String, Object> result = new HashMap<String, Object>();
		File importFile = new File(path);
      	log.info("func[importIn] importFile: " + importFile);
  		EvaluateObj evaluateObj = null ; 
  		int row = 1;
  		try{
            Workbook wb = WorkbookFactory.create(importFile);
            Sheet sheet = wb.getSheetAt(0);
            int endRow = sheet.getLastRowNum();
            for(; row<=endRow; row++){
            	Row row2 = sheet.getRow(row);
            	if (row2.getCell(1) == null) break;
            	evaluateObj = importRead(sheet,row);
            	if (StringUtil.isEmpty(evaluateObj.getGoodsId()) && StringUtil.isEmpty(evaluateObj.getCenterGoodsId())) {
            		if (StringUtil.isEmpty(evaluateObj.getMealId())) {
                		result.put("code", 0);
                        result.put("msg", "第"+(row+1)+"有问题,请检查一下");
                        return result;
                	}
                	if (getGoodsId(evaluateObj.getMealId()) == 0) {
                		result.put("code", 0);
                        result.put("msg", "套餐Id为"+evaluateObj.getMealId()+"找不到对应的goodsId,请检查一下");
                        return result;
                	}
            	}
            }
            result.put("code", 1);
        }catch (Exception e){
            log.error("evaluateObj: " + evaluateObj + ", row: " + row, e);
            result.put("code", 0);
            result.put("msg", "第"+(row+1)+"有问题,请检查一下");
        }
  		return result;
	}
    
	private void handleEvaluateObj(EvaluateObj evaluateObj) {
		if (StringUtil.isEmpty(evaluateObj.getUserId())) {
			evaluateObj.setUserId(String.valueOf(insertUser(Integer.valueOf(evaluateObj.getIsPharmacistEvaluate())).getId()));
    	}
		log.info("func[handleEvaluateObj] centerGoodsId["+evaluateObj.getCenterGoodsId()+"]");
		if (!StringUtil.isEmpty(evaluateObj.getMealId())) {
			evaluateObj.setGoodsId(String.valueOf(getGoodsId(evaluateObj.getMealId())));
    	} else if (!StringUtil.isEmpty(evaluateObj.getCenterGoodsId())) {
    		Map<String, Object> obj = this.getMealIdAndGoodsIdByMySql(evaluateObj.getCenterGoodsId());
    		log.info("obj: " + obj.toString());
    		String mealId = "0";
    		String goodsId = "0";
    		if (!StringUtil.isEmpty(obj.get("mealId"))) {
    			mealId = String.valueOf(obj.get("mealId"));
    			log.info("mealId: " + mealId);
    			evaluateObj.setMealId(mealId);
    		} else {
    			evaluateObj.setMealId(mealId);
    		}
    		if (!StringUtil.isEmpty(obj.get("goodsId"))) {
    			goodsId = String.valueOf(obj.get("goodsId"));
    			log.info("goodsId: " + goodsId);
    			evaluateObj.setGoodsId(goodsId);
    		} else {
    			goodsId = String.valueOf(this.getGoodsIdByMySql(evaluateObj.getCenterGoodsId()));
    			log.info("goodsId2: " + goodsId);
    			evaluateObj.setGoodsId(goodsId);
    		}
    	}
	}
	
    /**
     * 插入评价
     * @param evaluateObj
     */
    private void insertGoodsEvaluate(EvaluateObj evaluateObj, String startDate, String endDate) {
    	GoodsEvaluate goodsEvaluate = new GoodsEvaluate();
    	if (StringUtil.isEmpty(evaluateObj.getBuyTime())) {
    		goodsEvaluate.setBuyTime(null);
    	} else {
    		goodsEvaluate.setBuyTime(DateUtil.parseDate(evaluateObj.getBuyTime(), "yyyy-MM-dd"));
    	}
    	goodsEvaluate.setContent(evaluateObj.getContent());
    	if (StringUtil.isEmpty(evaluateObj.getCreateTime())) {
    		goodsEvaluate.setCreateTime(randomDate(startDate, endDate));
    	} else {
    		goodsEvaluate.setCreateTime(DateUtil.parseDate(evaluateObj.getCreateTime(), "yyyy-MM-dd"));
    	}
    	log.info("func[insertGoodsEvaluate] mealId["+evaluateObj.getMealId()+"] goodsId["+evaluateObj.getGoodsId()+"]");
    	goodsEvaluate.setMealId(Integer.valueOf(evaluateObj.getMealId()));
		goodsEvaluate.setGoodsId(Integer.valueOf(evaluateObj.getGoodsId()));
    	goodsEvaluate.setOrderId(null);
    	goodsEvaluate.setStatus(GoodsEvaluateEnum.AUDITED_PASS.getId());
    	goodsEvaluate.setUserId(Integer.valueOf(evaluateObj.getUserId()));
    	goodsEvaluate.setIsPharmacistEvaluate(Integer.valueOf(evaluateObj.getIsPharmacistEvaluate()));
    	goodsEvaluate.setType(3); // 人工导入标示
    	goodsIdList.add(goodsEvaluate.getGoodsId());
    	goodsEvaluateDao.insert(goodsEvaluate);
    }
    
    private UserAccountInfo insertUser(int isPharmacistEvaluate) {
		UserAccountInfo userAccountInfo = new UserAccountInfo();
		userAccountInfo.setAccountName(createAccountName());
		userAccountInfo.setIsAccountValid(UserAccountInfoStatusEnum.AVAILABILITY_YES.getId());
		userAccountInfo.setIsAccountActive(UserAccountInfoStatusEnum.STATUS_NORMAL.getId());
		Date date = new Date();
		userAccountInfo.setCreateTime(date.getTime());
		userAccountInfo.setExpandCode("evaluate_atuo_create");
		userAccountInfoDao.insert(userAccountInfo);
		initializeOther(userAccountInfo, "匿名用户", isPharmacistEvaluate);
		return userAccountInfo;
	}
    
    private String createAccountName() {
		String userName = "";
		String pre = "好药师";
	    boolean flag = false;
	    Map<String, Object> param = new HashMap<String, Object>();
	    int count = 0;
		do {
	       userName = pre + RandomString(7);
	       param.put("eqAccountName", userName);
	       count = userAccountInfoDao.selectCountByMap(param);
	       if (count > 0) {
	    	   flag = false;
	       } else {
	    	   flag = true;
	       }
		} while (!flag);
		return userName;
	}
	
	public static String RandomString(int length) {
		String str = "0123456789";
		Random random = new Random();
		StringBuffer buf = new StringBuffer();
		for (int i = 0; i < length; i++) {
			int num = random.nextInt(10);
			buf.append(str.charAt(num));
		}
		return buf.toString();
	}
    
    private void initializeOther(UserAccountInfo userAccountInfo, String nickname, int isPharmacistEvaluate) {
		UserBaseInfo userBaseInfo = new UserBaseInfo();
		UserLevelInfo userLevelInfo = new UserLevelInfo();
		userBaseInfo.setId(userAccountInfo.getId());
		if (isPharmacistEvaluate == GoodsEvaluateEnum.IS_PHARMACIST_EVALUATE_YES.getId()) {
			userLevelInfo.setLevelId(UserTypeEnum.PHARMACIST.getType());
		} else {
			userLevelInfo.setLevelId(UserTypeEnum.GENERAL.getType());
		}
		// TODO 默认的一个昵称
		if (StringUtil.isEmpty(nickname)) {
			userBaseInfo.setNickName(userAccountInfo.getAccountName());
		} else {
			userBaseInfo.setNickName(nickname);
		}
		if (!StringUtil.isEmpty(userAccountInfo.getPhone())) {
			JSONArray jsonArray = new JSONArray();
			JSONObject jsonObject = new JSONObject();
			jsonObject.put("isVerify", 1);
			jsonObject.put("phone", userAccountInfo.getPhone());
			jsonObject.put("isImport", 0);
			jsonArray.add(jsonObject);
			userBaseInfo.setOrderPhone(jsonArray.toJSONString());
		}
		userLevelInfo.setId(userAccountInfo.getId());
		userBaseInfoDao.insert(userBaseInfo);
		userLevelInfoDao.insert(userLevelInfo);
	}

    /**
     * 获取商品id
     * @param mealId
     * @return
     */
    private Integer getGoodsId(String mealId) {
		String sendUrl="http://api.uc.frogshealth.com/v1/meal/partialList.jsonp";
		String[] keyvals = {
				ApiUrlParamEnum.ids.getParamName(),mealId,
				"uuid","ucenter",
				"token","ucenter_)$02",
			};
		try {
			String result = Jsoup.connect(sendUrl).method(Method.POST).data(keyvals).ignoreContentType(true).timeout(5000).execute().body();
			JSONObject object = JSONObject.parseObject(result);
			if (ApiResultEnum.SUCCESS.getId() == object.getJSONObject("response").getIntValue(ResultParamEnum.code.getParamName())) {
				JSONArray goodsArrayJson = object.getJSONObject("response").getJSONArray("list");
		    	if(null != goodsArrayJson && goodsArrayJson.size() > 0 ) {
		    		JSONObject goods = (JSONObject) goodsArrayJson.get(0);
		    		Integer goodsId = goods.getInteger("mainGoodsId");
		    		return goodsId;
				}
			} else {
				return 0;
			}
		} catch (IOException e) {
			log.error("func[sendSms] url[{}]  exception[{} - {}] desc[fail]",
					new Object[] { sendUrl,  mealId, e.getMessage(), Arrays.deepToString(e.getStackTrace()) });
		}
		return 0;
	}
    
    private static Date randomDate(String beginDate, String endDate) {
    	try {
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
			Date start = format.parse(beginDate);
			Date end = format.parse(endDate);
			if (start.getTime() >= end.getTime()) {
				return null;
			}
			long date = random(start.getTime(), end.getTime());
			return new Date(date);
		} catch (Exception e) {
			log.error("func[randomDate] beginDate[" + beginDate + "] endDate[" + endDate + "] e["+e.getStackTrace()+"] desc[fail]");
		}
    	return null;
    }
    
    private static long random(long begin, long end) {
    	long rtn = begin + (long) (Math.random() * (end - begin));
    	if (rtn == begin || rtn == end) {
    		return random(begin, end);
    	}
    	return rtn;
    }
    
    public EvaluateObj importRead(Sheet sheet,int rowIndex){

    	EvaluateObj evaluateObj = new EvaluateObj();
        Row row = sheet.getRow(rowIndex);

        // 商品Id
        Cell cell0 = row.getCell(0);
        if (cell0 != null) {
            if(cell0.getCellType() == Cell.CELL_TYPE_STRING){
            	evaluateObj.setGoodsId(row.getCell(0).getStringCellValue().trim());
            }else if(cell0.getCellType() == Cell.CELL_TYPE_NUMERIC){
            	evaluateObj.setGoodsId(String.valueOf((int)(row.getCell(0).getNumericCellValue())));
            }
        }
        
        // 套餐Id
        Cell cell1 = row.getCell(1);
        log.info("func[importRead] rowIndex[" + rowIndex + "], mealId[" + row.getCell(1) + "]");
        if (cell1 != null) {
        	if(cell1.getCellType() == Cell.CELL_TYPE_STRING){
        		evaluateObj.setMealId(row.getCell(1).getStringCellValue().trim());
            }else if(cell1.getCellType() == Cell.CELL_TYPE_NUMERIC){
            	evaluateObj.setMealId(String.valueOf((int)(row.getCell(1).getNumericCellValue())));
            }
        }
        
        // 用户Id
        Cell cell2 = row.getCell(2);
        if (cell2 != null) {
        	if(cell2.getCellType() == Cell.CELL_TYPE_STRING){
        		evaluateObj.setUserId(row.getCell(2).getStringCellValue().trim());
            }else if(cell2.getCellType() == Cell.CELL_TYPE_NUMERIC){
            	evaluateObj.setUserId(String.valueOf((int)(row.getCell(2).getNumericCellValue())));
            }
        }
        
        // 评价内容
        evaluateObj.setContent(row.getCell(3).getStringCellValue().trim());
        
        // 是否药师评价
        Cell cell4 = row.getCell(4);
        if (cell4 != null) {
        	if(cell4.getCellType() == Cell.CELL_TYPE_STRING){
        		evaluateObj.setIsPharmacistEvaluate(row.getCell(4).getStringCellValue().trim());
            }else if(cell4.getCellType() == Cell.CELL_TYPE_NUMERIC){
            	evaluateObj.setIsPharmacistEvaluate(String.valueOf((int)(row.getCell(4).getNumericCellValue())));
            }
        }
        if (row.getCell(5) != null) {
        	evaluateObj.setCreateTime(row.getCell(5).getStringCellValue().trim());
        }
        if (row.getCell(6) != null) {
        	evaluateObj.setTag(row.getCell(6).getStringCellValue().trim());
        }
        if (row.getCell(7) != null) {
        	evaluateObj.setBuyTime(row.getCell(7).getStringCellValue().trim());
        }
        if (row.getCell(8) != null) {
        	evaluateObj.setOrderId(row.getCell(8).getStringCellValue().trim());
        }
        
        // 评分
        Cell cell9 = row.getCell(9);
        if (cell9 != null) {
        	if(cell9.getCellType() == Cell.CELL_TYPE_STRING){
        		evaluateObj.setScore(row.getCell(9).getStringCellValue().trim());
            }else if(cell9.getCellType() == Cell.CELL_TYPE_NUMERIC){
            	evaluateObj.setScore(String.valueOf((int)(row.getCell(9).getNumericCellValue())));
            }
        }
        
        // 商品中心商品id
        Cell cell10 = row.getCell(10);
        if (cell10 != null) {
        	if(cell10.getCellType() == Cell.CELL_TYPE_STRING){
        		evaluateObj.setCenterGoodsId(row.getCell(10).getStringCellValue().trim());
            }else if(cell10.getCellType() == Cell.CELL_TYPE_NUMERIC){
            	evaluateObj.setCenterGoodsId(String.valueOf((int)(row.getCell(10).getNumericCellValue())));
            }
        }

        return evaluateObj;
    }
    
    /**
     * 获取套餐id和商品id
     * @param centerGoodsId
     * @return
     */
    private Map<String, Object> getMealIdAndGoodsIdByMySql(String centerGoodsId) {
    	Map<String, Object> obj = new HashMap<String, Object>();
    	try {
    		String sql = "select meal_id as mealId, main_goods_id as goodsId from temp_mall_meal where main_center_goods_id = " +centerGoodsId + " limit 1;";
    		obj = jdbcTemplate.queryForMap(sql);
    	} catch (Exception e) {
    		log.error("func[getMealIdAndGoodsIdByMySql] e["+e.getStackTrace()+"] desc[fail]");
    	}
    	return obj;
    }
    
    /**
     * 获取商品id
     * @param centerGoodsId
     * @return
     */
    private Integer getGoodsIdByMySql(String centerGoodsId) {
    	Integer goodsId = 0;
    	try {
    		String sql = "select goods_id from temp_mall_goods where center_goods_id = " +centerGoodsId + " limit 1;";
    		goodsId = jdbcTemplate.queryForInt(sql);
    	} catch (Exception e) {
    		log.error("func[getGoodsIdByMySql] e["+e.getStackTrace()+"] desc[fail]");
    	}
    	return goodsId;
    }
    
}
